from flask import Flask, render_template, request, redirect, url_for, session, jsonify
from mysql_util import MysqlUtil
import pandas as pd

app = Flask(__name__)
app.secret_key = 'mrsoft12345678'  # 设置秘钥
import random
import os


@app.route('/')
def index():
    db = MysqlUtil()
    sql = 'SELECT * FROM category'
    categorys = db.fetchall(sql)  # 获取多条记录

    db = MysqlUtil()
    # sql = 'SELECT * FROM product'
    sql = 'SELECT * FROM product_temp'
    comment_products = db.fetchall(sql)  # 获取多条记录

    db = MysqlUtil()
    price=0;num=0;
    sql = 'SELECT * FROM shop'
    comment_products1= db.fetchall(sql)
    for i in range(0, len(comment_products1)):
        price+=comment_products1[i]['price']
        num+=1
    #print(comment_products)

    return render_template("/index.html", categorys=categorys,comment_products=comment_products,comment_products1=comment_products1,num=num,price=price)
    # return render_template("/test/index.html", categorys=categorys)


def allowed_file(filename):
    ALLOWED_EXTENSIONS = {'jpg', 'png', 'jpeg', "bmp"}
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@app.route('/index2')
def index2():
    db = MysqlUtil()
    price = 0;
    num = 0;
    sql = 'SELECT * FROM shop'
    comment_products1 = db.fetchall(sql)
    for i in range(0, len(comment_products1)):
        price += comment_products1[i]['price']
    return render_template("index2.html",comment_products1=comment_products1,num=num,price=price)

@app.route('/index')
def index1():
    return render_template("index.html")

@app.route('/job')
def job():
    return render_template("job.html")

@app.route('/product_board')
def product_board():
    db = MysqlUtil()
    # sql = 'SELECT * FROM product'
    sql = 'SELECT * FROM product_temp'
    products = db.fetchall(sql)  # 获取多条记录
    print(products)
    db = MysqlUtil()
    price = 0;
    num = 0;
    sql = 'SELECT * FROM shop'
    comment_products1 = db.fetchall(sql)
    for i in range(0, len(comment_products1)):
        price += comment_products1[i]['price']


    return render_template("product_board.html", products=products,comment_products1=comment_products1,num=num,price=price)
    # return render_template("/test/product_board_static.html",products=products)


@app.route('/add_product', methods=['GET', 'POST'])
def add_product():
    db = MysqlUtil()
    sql = 'SELECT * FROM category'
    categorys = db.fetchall(sql)  # 获取多条记录
    if (request.method == "POST"):
        pname = request.form.get("pname")
        pDesc = request.form.get("pDesc")
        counts = request.form.get("counts")
        old_price = request.form.get("old_price")
        new_price = request.form.get("new_price")

        file = request.files['file']
        if file.filename == '':
            return '没有选择文件', 400
        if file and allowed_file(file.filename):
            filename = file.filename
            file_name, file_ext = os.path.splitext(os.path.basename(filename))
            # print(file_name)
            print(file_ext)
            dir_name = "./static/product_test/"
            new_name = str(random.randint(1, 10000)) + file_ext
            print(dir_name)
            images_path = os.path.join(dir_name, new_name)
            print(images_path)
            file.save(images_path)

        id = "%20d" % random.randint(0, 1000000000)
        # images_path = "/static/product_test/2.jpg"
        db = MysqlUtil()  # 实例化数据库操作类
        sql = "INSERT INTO product_temp(id,pname,old_price,new_price,counts,images) \
               VALUES ('%s', '%s', '%s','%s','%s','%s')" % (
        id, pname, old_price, new_price, counts, images_path)  # 插入数据的SQL语句
        db.insert(sql)


        db = MysqlUtil()
        price = 0;
        num = 0;
        sql = 'SELECT * FROM shop'
        comment_products1 = db.fetchall(sql)
        for i in range(0, len(comment_products1)):
            price += comment_products1[i]['price']

        return redirect(url_for('product_board'))
    else:
        db = MysqlUtil()
        price = 0;
        num = 0;
        sql = 'SELECT * FROM shop'
        comment_products1 = db.fetchall(sql)
        for i in range(0, len(comment_products1)):
            price += comment_products1[i]['price']
        return render_template("/add_product.html", categorys=categorys,comment_products1=comment_products1,num=num,price=price)


@app.route('/product_temp/<string:id>', methods=['POST'])
def delete_product(id):
    db = MysqlUtil() # 实例化数据库操作类
    sql = "DELETE FROM product_temp WHERE id = '%s'" % (id) # 执行删除笔记的SQL语句
    db.delete(sql) # 删除数据库
    return redirect(url_for('product_board'))



#注册
@app.route('/register', methods=['GET', 'POST'])
def register():
    if (request.method == "POST"):
        username = request.form['name']
        password = request.form['password']
        email = request.form['email']

        print(username)
        print(password)
        print(email)

        db = MysqlUtil()
        sql = "INSERT INTO user_copy(username,password,email) \
        VALUES ('%s', '%s', '%s')" % (username, password, email)  # 插入数据的SQL语句

        product_list = db.insert(sql)  # 获取多条记录

        print(product_list)

        # insert_data(username, password, email)
        # return render_template("index.html")
        return redirect(url_for('index'))

    else:  #GET
        return render_template("register.html")

#登录
@app.route('/login', methods=['GET', 'POST'])
def login():
    if (request.method == "POST"):
        print("ok")
        username = request.form['username']
        password_candidate = request.form['password']
        print(username)
        sql = "SELECT * FROM user_copy  WHERE username = '%s'" % (username)  # 根据用户名查找user表中记录
        db = MysqlUtil()  # 实例化数据库操作类
        result = db.fetchone(sql)  # 获取一条记录
        print(password_candidate)
        print(result)
        db_password = result['password']  # 用户填写的密码
        if password_candidate == db_password:
            # 写入session
            session['logged_in'] = True
            session['username'] = username

            # return "登录成功"# 跳转到控制台
            return redirect(url_for('index'))
        else:
            print("密码错误")
            return render_template("login.html")

    else:  #GET
        return render_template("login.html")


@app.route('/logout')
def logout():
    session.clear()  # 清除Session
    return redirect(url_for('index'))


@app.route('/get_company', methods=['GET'])
def get_company():
    json = request.json
    print('recv:', json)
    re_data = {
           'company_num': 1001,
           'job_num': 5001,
           'avg_salary': 50001,
    }
    return jsonify(re_data)

@app.route('/get_industry', methods=['GET'])
def get_industry():


    #读取数据库文件
    db = MysqlUtil()    #连接数据库
    sql = 'SELECT pname,new_price FROM product_temp'    #读取文件
    df = db.fetchall(sql)               #将读取到的文件存储到df

    new_price_list = []
    pname_list = []
    for i in range(0, len(df)):
        pname_list.append(df[i]['pname'])
        new_price_list.append(df[i]['new_price'])
    print(new_price_list)
    print(pname_list)
    re_data = {
           # 'industry_type': ["广东", "广东", "广东"],
           'industry_type': pname_list,
           'industry_type_value':  new_price_list,
    }

    return jsonify(re_data)

@app.route('/submit-config', methods=['POST'])
def submit_config():
    config = request.form['config']
    color = request.form['color']
    price = request.form.get('price')
    print('Received config:', config)
    print('Received color:', color)
    print('Received price:', price)\

    id = "%20d" % random.randint(0, 1000000000)
    # images_path = "/static/product_test/2.jpg"
    db = MysqlUtil()  # 实例化数据库操作类
    sql = "INSERT INTO shop(id,color,price,config) \
                   VALUES ('%s', '%s', '%s','%s')" % (
        id, color, price, config)  # 插入数据的SQL语句
    db.insert(sql)
    # print(product_list)
    return 'Received config: {} and color: {}'.format(config, color)



app.run(debug=True)
